﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Text.model;
using System.Data;
using System.Data.SqlClient;

namespace Test.DAL
{
  public class StuInfoDAL
    {
        public static List<StuInfo> Select(string name = "")
        {
            //创建SQL语句
            var sql = "select * from StuInfo where name like @name";
            //引用参数
            SqlParameter sqlParameter = new SqlParameter("@name", $"%{name}%");
            //调用sql语句
            DataTable table = SqlHelper.Query(sql, sqlParameter);
            List<StuInfo> list = new List<StuInfo>();
            foreach (DataRow row in table.Rows)
            {
                var cls = new StuInfo()
                {
                    Id = Convert.ToInt32(row["Id"]),
                    Name = Convert.ToString(row["Name"]),
                    Comment = Convert.ToString(row["Comment"])
                };
                list.Add(cls);
            }
            return list;
        }
        /// <summary>
        /// 根据id查找
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static StuInfo Select(int id)
        {
            var sql = "select * from StuInfo where id=@id";
            SqlParameter[] sqlParameters = new SqlParameter[]
            {
                new SqlParameter("@id",id)
             };
            SqlDataReader reader = SqlHelper.Reader(sql, sqlParameters);
            //设置一个空类
            StuInfo info = null;
            if (reader.Read())
            {
                info = new StuInfo()
                {
                    Id = Convert.ToInt32(reader["Id"]),
                    Name = Convert.ToString(reader["Name"]),
                    Comment = Convert.ToString(reader["Comment"])
                };
            }
            reader.Close();
            return info;
        }
        public static int Insert(StuInfo info)
        {
            var sql = "insert into StuInfo values(@Name,@Comment)";
            SqlParameter[] sqlParameters = new SqlParameter[]
            {
                new SqlParameter("@name",info.Name),
                new SqlParameter("@Comment",info.Comment)
            };
            return SqlHelper.NonQuery(sql, sqlParameters);
        }
        public static int Delete(int id)
        {
            var sql = "delete from StuInfo where id=@id";
            SqlParameter[] sqlParameters = new SqlParameter[]
            {
                new SqlParameter("@id",id)
            };
            return SqlHelper.NonQuery(sql, sqlParameters);
        }
        public static int Update(StuInfo info)
        {
            var sql = "UPDATE StuInfo set Name=@Name,Comment=@Comment where id=@id";
            SqlParameter[] sqlParameters = new SqlParameter[]
            {
                new SqlParameter("@id",info.Id),
                new SqlParameter("@name",info.Name),
                new SqlParameter("@Comment",info.Comment)
            };
            return SqlHelper.NonQuery(sql, sqlParameters);
        }
    }
}
